package com.web3.management.repository;

import com.web3.management.entity.TradeLog;
import com.web3.management.repository.projection.AccountTradeProgressProjection;
import com.web3.management.repository.projection.DailyTradeStatsProjection;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;

@Repository
public interface TradeLogRepository extends JpaRepository<TradeLog, Integer>, JpaSpecificationExecutor<TradeLog> {

    @Query("SELECT t.tradeDate AS tradeDate, " +
            "COALESCE(SUM(t.plannedAmount), 0) AS totalPlannedAmount, " +
            "COALESCE(SUM(CASE WHEN t.direction = 'BUY' THEN t.actualAmount ELSE 0 END), 0) AS totalActualAmount, " +
            "COALESCE(SUM(t.slippage), 0) AS totalSlippage " +
            "FROM TradeLog t " +
            "WHERE (:accountId IS NULL OR t.accountId = :accountId) " +
            "AND (:accountConfigId IS NULL OR t.accountConfigId = :accountConfigId) " +
            "AND t.tradeDate BETWEEN :startDate AND :endDate " +
            "GROUP BY t.tradeDate " +
            "ORDER BY t.tradeDate")
    List<DailyTradeStatsProjection> aggregateDailyStats(@Param("accountId") Integer accountId,
                                                       @Param("accountConfigId") Integer accountConfigId,
                                                       @Param("startDate") LocalDate startDate,
                                                       @Param("endDate") LocalDate endDate);

    @Query("SELECT t.tradeDate AS tradeDate, " +
            "COALESCE(SUM(t.plannedAmount), 0) AS totalPlannedAmount, " +
            "COALESCE(SUM(CASE WHEN t.direction = 'BUY' THEN t.actualAmount ELSE 0 END), 0) AS totalActualAmount, " +
            "COALESCE(SUM(t.slippage), 0) AS totalSlippage " +
            "FROM TradeLog t " +
            "WHERE t.tradeDate = :tradeDate " +
            "GROUP BY t.tradeDate")
    List<DailyTradeStatsProjection> aggregateByDate(@Param("tradeDate") LocalDate tradeDate);

    @Query("SELECT t.accountId AS accountId, " +
            "COALESCE(SUM(CASE WHEN t.direction = 'BUY' THEN t.actualAmount ELSE 0 END), 0) AS totalActualAmount, " +
            "COALESCE(SUM(t.slippage), 0) AS totalSlippage " +
            "FROM TradeLog t " +
            "WHERE t.tradeDate = :tradeDate " +
            "GROUP BY t.accountId")
    List<AccountTradeProgressProjection> aggregateAccountSummaryByDate(@Param("tradeDate") LocalDate tradeDate);

    /**
     * 查询异常交易记录
     */
    Page<TradeLog> findByIsAbnormalTrueOrderByTradeTimeDesc(Pageable pageable);

    /**
     * 根据账号查询异常交易记录
     */
    Page<TradeLog> findByAccountIdAndIsAbnormalTrueOrderByTradeTimeDesc(Integer accountId, Pageable pageable);

    /**
     * 统计今日交易数量
     */
    @Query("SELECT COUNT(t) FROM TradeLog t WHERE t.tradeDate = :date")
    long countByTradeDate(@Param("date") LocalDate date);

    /**
     * 统计今日异常交易数量
     */
    @Query("SELECT COUNT(t) FROM TradeLog t WHERE t.tradeDate = :date AND t.isAbnormal = true")
    long countAbnormalByTradeDate(@Param("date") LocalDate date);

    /**
     * 统计今日告警次数
     */
    @Query("SELECT COUNT(t) FROM TradeLog t WHERE t.tradeDate = :date AND t.alertSent = true")
    long countAlertSentByTradeDate(@Param("date") LocalDate date);

    /**
     * 计算平均磨损率
     */
    @Query("SELECT AVG(t.slippageRate) FROM TradeLog t WHERE t.tradeDate = :date AND t.slippageRate IS NOT NULL")
    Double getAverageSlippageRateByDate(@Param("date") LocalDate date);
}
